﻿/*
* CRM_Customer.cs
*
* 功 能： N/A
* 类 名： CRM_Customer
*
* Ver    变更日期             负责人     变更内容
* ───────────────────────────────────
* V1.0  2015-06-23 18:38:21    黄润伟    
*
* Copyright (c) 2015 www.xhdcrm.com   All rights reserved.
*┌──────────────────────────────────┐
*│　版权所有：黄润伟                      　　　　　　　　　　　　　　│
*└──────────────────────────────────┘
*/

using System;
using System.Data;
using System.Web;
using XHD.Common;
using XHD.Controller;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System.IO;

namespace XHD.Server
{
    public class CRM_Customer
    {
        public static BLL.CRM_Customer customer = new BLL.CRM_Customer();
        public static Model.CRM_Customer model = new Model.CRM_Customer();

        public HttpContext Context;
        public int emp_id;
        public string emp_name;
        public Model.hr_employee employee;
        public HttpRequest request;
        public string uid;


        public CRM_Customer()
        {
        }

        public CRM_Customer(HttpContext context)
        {
            Context = context;
            request = context.Request;

            var userinfo = new User_info();
            employee = userinfo.GetCurrentEmpInfo(context);

            emp_id = employee.ID;
            emp_name = PageValidate.InputText(employee.name, 50);
            uid = PageValidate.InputText(employee.uid, 50);
        }

        //save
        public void save()
        {
            model.Customer = PageValidate.InputText(request["T_company"], 255);
            model.address = PageValidate.InputText(request["T_address"], 255);
            model.fax = PageValidate.InputText(request["T_fax"], 255);
            model.site = PageValidate.InputText(request["T_Website"], 255);

            model.tel = PageValidate.InputText(request["T_company_tel"], 255);

            if (PageValidate.IsNumber(request["T_industry_val"]))
                model.industry_id = int.Parse(request["T_industry_val"]);

            if (PageValidate.IsNumber(request["T_Provinces_val"]))
                model.Provinces_id = int.Parse(request["T_Provinces_val"]);

            if (PageValidate.IsNumber(request["T_City_val"]))
                model.City_id = int.Parse(request["T_City_val"]);

            if (PageValidate.IsNumber(request["T_customertype_val"]))
                model.CustomerType_id = int.Parse(request["T_customertype_val"]);

            if (PageValidate.IsNumber(request["T_customerlevel_val"]))
                model.CustomerLevel_id = int.Parse(request["T_customerlevel_val"]);

            if (PageValidate.IsNumber(request["T_CustomerSource_val"]))
                model.CustomerSource_id = int.Parse(request["T_CustomerSource_val"]);

            model.DesCripe = PageValidate.InputText(request["T_descript"], 4000);
            model.Remarks = PageValidate.InputText(request["T_remarks"], 4000);
            model.privatecustomer = PageValidate.InputText(request["T_private"], 255);
            model.xy = PageValidate.InputText(request["T_xy"], 50).Trim(' ');

            if (PageValidate.IsNumber(request["T_dep_val"]))
                model.Department_id = int.Parse(request["T_dep_val"]);

            if (PageValidate.IsNumber(request["T_employee_val"]))
                model.Employee_id = int.Parse(request["T_employee_val"]);

            string id = PageValidate.InputText(request["id"], 50);
            if (PageValidate.IsNumber(id))
            {
                DataSet ds = customer.GetList("id=" + int.Parse(id));
                DataRow dr = ds.Tables[0].Rows[0];

                model.Serialnumber = PageValidate.InputText(dr["Serialnumber"].ToString(), 255);

                model.id = int.Parse(id);
                customer.Update(model);

                //日志
                var log = new sys_log();

                int UserID = emp_id;
                string UserName = emp_name;
                string IPStreet = request.UserHostAddress;
                string EventTitle = model.Customer;
                string EventType = "客户修改";
                int EventID = model.id;
                string Log_Content = null;

                if (dr["Customer"].ToString() != request["T_company"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "客户名", dr["Customer"], request["T_company"]);

                if (dr["address"].ToString() != request["T_address"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "地址", dr["address"], request["T_address"]);

                if (dr["fax"].ToString() != request["T_fax"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "传真", dr["fax"], request["T_fax"]);

                if (dr["site"].ToString() != request["T_Website"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "网址", dr["site"], request["T_Website"]);

                if (dr["industry"].ToString() != request["T_industry"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "行业", dr["industry"], request["T_industry"]);

                if (dr["Provinces"].ToString() != request["T_Provinces"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "省份", dr["Provinces"], request["T_Provinces"]);

                if (dr["City"].ToString() != request["T_City"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "城市", dr["City"], request["T_City"]);

                if (dr["CustomerType"].ToString() != request["T_customertype"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "客户类型", dr["CustomerType"], request["T_customertype"]);

                if (dr["CustomerLevel"].ToString() != request["T_customerlevel"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "客户级别", dr["CustomerLevel"], request["T_customerlevel"]);

                if (dr["CustomerSource"].ToString() != request["T_CustomerSource"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "客户来源", dr["CustomerSource"], request["T_CustomerSource"]);

                if (dr["DesCripe"].ToString() != request["T_descript"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "客户描述", dr["DesCripe"], request["T_descript"]);

                if (dr["Remarks"].ToString() != request["T_remarks"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "备注", dr["Remarks"], request["T_remarks"]);

                if (dr["privatecustomer"].ToString() != request["T_private"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "公私", dr["privatecustomer"], request["T_private"]);

                if (dr["Department"].ToString() != request["T_dep"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "部门", dr["Department"], request["T_dep"]);

                if (dr["Employee"].ToString() != request["T_employee1"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "员工", dr["Employee"], request["T_employee1"]);

                if (dr["xy"].ToString() != request["T_xy"])
                    Log_Content += string.Format("【{0}】{1} → {2} \n", "坐标", dr["xy"], request["T_xy"]);

                if (!string.IsNullOrEmpty(Log_Content))
                    log.Add_log(UserID, UserName, IPStreet, EventTitle, EventType, EventID, Log_Content);
            }
            else
            {
                model.isDelete = 0;
                DateTime nowtime = DateTime.Now;
                model.Create_date = nowtime;
                model.Serialnumber = nowtime.AddMilliseconds(3).ToString("yyyyMMddHHmmssfff").Trim();
                model.Create_id = emp_id;
                model.Create_name = PageValidate.InputText(emp_name, 255);
                int customerid = customer.Add(model);

                var contact = new BLL.CRM_Contact();
                var modelcontact = new Model.CRM_Contact();
                modelcontact.isDelete = 0;
                modelcontact.C_name = PageValidate.InputText(request["T_customername"], 255);
                modelcontact.C_sex = PageValidate.InputText(request["T_sex"], 255);
                modelcontact.C_department = PageValidate.InputText(request["T_contact_dep"], 255);
                modelcontact.C_position = PageValidate.InputText(request["T_contact_position"], 255);
                modelcontact.C_QQ = PageValidate.InputText(request["T_qq"], 255);
                modelcontact.C_tel = PageValidate.InputText(request["T_tel"], 255);
                modelcontact.C_mob = PageValidate.InputText(request["T_mobil"], 255);
                modelcontact.C_email = PageValidate.InputText(request["T_email"], 255);
                modelcontact.C_customerid = customerid;
                modelcontact.C_createId = emp_id;
                modelcontact.C_createDate = DateTime.Now;
                modelcontact.C_hobby = PageValidate.InputText(request["T_hobby"], 1000);
                modelcontact.C_remarks = PageValidate.InputText(request["T_contact_remarks"], int.MaxValue);
                int ct = contact.Add(modelcontact);

                //return(ct);
            }
        }

        public string grid()
        {
            int PageIndex = int.Parse(request["page"] == null ? "1" : request["page"]);
            int PageSize = int.Parse(request["pagesize"] == null ? "30" : request["pagesize"]);
            string sortname = request["sortname"];
            string sortorder = request["sortorder"];

            if (string.IsNullOrEmpty(sortname))
                sortname = " id";
            if (string.IsNullOrEmpty(sortorder))
                sortorder = " desc";

            string sorttext = " " + sortname + " " + sortorder;

            string Total;
            string serchtxt = null;
            string serchtype = request["isdel"];
            if (serchtype == "1")
                serchtxt += " isDelete=1 ";
            else
                serchtxt += " isDelete=0 ";

            if (PageValidate.IsNumber(request["companyid"]))
                serchtxt += $" and id = {int.Parse(request["companyid"])}";

            if (!string.IsNullOrEmpty(request["company"]))
                serchtxt += $" and customer like N'%{PageValidate.InputText(request["company"], 255)}%'";

            if (!string.IsNullOrEmpty(request["address"]))
                serchtxt += $" and address like N'%{PageValidate.InputText(request["address"], 255)}%'";

            if (PageValidate.IsNumber(request["industry_val"]))
                serchtxt += $" and industry_id = {int.Parse(request["industry_val"])}";

            if (!string.IsNullOrEmpty(request["tel"]))
                serchtxt += string.Format(" and tel like N'%{0}%'", PageValidate.InputText(request["tel"], 255));

            if (!string.IsNullOrEmpty(request["mobil"]))
                serchtxt += string.Format(" and mobil like N'%{0}%'", PageValidate.InputText(request["mobil"], 255));

            if (!string.IsNullOrEmpty(request["qq"]))
                serchtxt += string.Format(" and QQ like N'%{0}%'", PageValidate.InputText(request["qq"], 255));

            string keyword = PageValidate.InputText(request["keyword"], 500);
            if (!string.IsNullOrEmpty(keyword) && keyword != "输入关键词搜索地址、描述、备注")
                serchtxt +=
                    string.Format(" and ( address like N'%{0}%' or DesCripe like N'%{0}%' or Remarks like N'%{0}%' ) ",
                        keyword);

            if (PageValidate.IsNumber(request["customertype_val"]))
                serchtxt += string.Format(" and CustomerType_id = {0} ", int.Parse(request["customertype_val"]));

            if (PageValidate.IsNumber(request["customerlevel_val"]))
                serchtxt += string.Format(" and CustomerLevel_id = {0} ", int.Parse(request["customerlevel_val"]));

            if (PageValidate.IsNumber(request["cus_sourse_val"]))
                serchtxt += string.Format(" and CustomerSource_id = {0} ", int.Parse(request["cus_sourse_val"]));

            if (PageValidate.IsNumber(request["T_Provinces_val"]))
                serchtxt += string.Format(" and Provinces_id = {0}", int.Parse(request["T_Provinces_val"]));

            if (PageValidate.IsNumber(request["T_City_val"]))
                serchtxt += string.Format(" and City_id = {0}", int.Parse(request["T_City_val"]));

            if (PageValidate.IsNumber(request["department_val"]))
                serchtxt += string.Format(" and Department_id = {0} ", int.Parse(request["department_val"]));

            if (PageValidate.IsNumber(request["employee_val"]))
                serchtxt += string.Format(" and Employee_id = {0}", int.Parse(request["employee_val"]));

            if (!string.IsNullOrEmpty(request["startdate"]))
                serchtxt += string.Format(" and Create_date >= '{0}'", PageValidate.InputText(request["startdate"], 255));

            if (!string.IsNullOrEmpty(request["enddate"]))
            {
                DateTime enddate = DateTime.Parse(request["enddate"]).AddHours(23).AddMinutes(59).AddSeconds(59);
                serchtxt += string.Format(" and Create_date <= '{0}'", enddate);
            }

            if (!string.IsNullOrEmpty(request["startdate_del"]))
                serchtxt += string.Format(" and Delete_time >= '{0}'",
                    PageValidate.InputText(request["startdate_del"], 255));

            if (!string.IsNullOrEmpty(request["enddate_del"]))
            {
                DateTime enddatedel = DateTime.Parse(request["enddate_del"]).AddHours(23).AddMinutes(59).AddSeconds(59);
                serchtxt += string.Format(" and Delete_time <= '{0}'", enddatedel);
            }

            if (!string.IsNullOrEmpty(request["startfollow"]))
                serchtxt += string.Format(" and lastfollow >= '{0}'",
                    PageValidate.InputText(request["startfollow"], 255));

            if (!string.IsNullOrEmpty(request["endfollow"]))
            {
                DateTime enddate = DateTime.Parse(request["endfollow"]).AddHours(23).AddMinutes(59).AddSeconds(59);
                serchtxt += string.Format(" and lastfollow <= '{0}'", enddate);
            }

            if (request["type"] == "map")
                serchtxt += " and xy is not null";
            else if (request["type"] == "repeat")
            {
                serchtxt += " and Customer in (select Customer from (select Customer from CRM_Customer where isDelete=0) abc group by Customer having count(1)>=2  )";
                sorttext = " Customer,id desc";
            }
            //权限
            serchtxt += DataAuth();

            //return (serchtxt);

            DataSet ds = customer.GetList(PageSize, PageIndex, serchtxt, sorttext, out Total);

            string dt = GetGridJSON.DataTableToJSON1(ds.Tables[0], Total);
            return (dt);
        }

        public string getMapList()
        {
            string serchtxt = " isDelete=0 and xy is not null";
            //权限
            serchtxt += DataAuth();
            DataSet ds = customer.GetMapList(serchtxt);
            string dt = GetGridJSON.DataTableToJSON(ds.Tables[0]);
            return dt;
        }

        //Form JSON
        public string form(string id)
        {
            string dt;
            if (PageValidate.IsNumber(id))
            {
                DataSet ds = customer.GetList("id=" + id + DataAuth());
                dt = DataToJson.DataToJSON(ds);
            }
            else
            {
                dt = "{}";
            }

            return (dt);
        }

        public string count(string id)
        {
            DataSet ds = customer.GetList("id=" + int.Parse(id));

            var contact = new BLL.CRM_Contact();
            var contract = new BLL.CRM_contract();
            var order = new BLL.CRM_order();
            var follow = new BLL.CRM_Follow();

            int contactcount = 0, contractcount = 0, followcount = 0, ordercount = 0;
            contractcount = contract.GetList(" Customer_id=" + int.Parse(id)).Tables[0].Rows.Count;
            contactcount = contact.GetList(" C_customerid=" + int.Parse(id)).Tables[0].Rows.Count;
            followcount = follow.GetList(" Customer_id=" + int.Parse(id)).Tables[0].Rows.Count;
            ordercount = order.GetList(" Customer_id=" + int.Parse(id)).Tables[0].Rows.Count;

            return (string.Format("{0}联系人, {2}跟进, {3}订单，{1}合同 ", contactcount, contractcount, followcount, ordercount));
        }

        //预删除
        public string AdvanceDelete(int id)
        {
            DataSet ds = customer.GetList("id=" + id);

            var contact = new BLL.CRM_Contact();
            var contract = new BLL.CRM_contract();
            var order = new BLL.CRM_order();
            var follow = new BLL.CRM_Follow();

            int contactcount = 0, contractcount = 0, followcount = 0, ordercount = 0;
            contractcount = contract.GetList(" isDelete=0 and Customer_id=" + (id)).Tables[0].Rows.Count;
            contactcount = contact.GetList(" isDelete=0 and C_customerid=" + (id)).Tables[0].Rows.Count;
            followcount = follow.GetList(" isDelete=0 and Customer_id=" + (id)).Tables[0].Rows.Count;
            ordercount = order.GetList(" isDelete=0 and Customer_id=" + (id)).Tables[0].Rows.Count;

            if (contractcount > 0 || contactcount > 0 || followcount > 0 || ordercount > 0)
            {
                string returntxt = "";
                if (contactcount > 0) returntxt += string.Format("{0}联系人 ", contactcount);
                if (followcount > 0) returntxt += string.Format("{0}跟进 ", followcount);
                if (ordercount > 0) returntxt += string.Format("{0}订单 ", ordercount);
                if (contractcount > 0) returntxt += string.Format("{0}合同 ", contractcount);
                return (returntxt);
            }

            bool canedel = true;
            if (uid != "admin")
            {
                var dataauth = new GetDataAuth();
                string txt = dataauth.GetDataAuthByid("1", "Sys_del", emp_id);

                string[] arr = txt.Split(':');
                switch (arr[0])
                {
                    case "none":
                        canedel = false;
                        break;
                    case "my":
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            if (ds.Tables[0].Rows[i]["Employee_id"].ToString() == arr[1])
                                canedel = true;
                            else
                                canedel = false;
                        }
                        break;
                    case "dep":
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            if (ds.Tables[0].Rows[i]["Department_id"].ToString() == arr[1])
                                canedel = true;
                            else
                                canedel = false;
                        }
                        break;
                    case "all":
                        canedel = true;
                        break;
                }
            }
            if (canedel)
            {
                bool isdel = customer.AdvanceDelete(id, 1, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                if (isdel)
                {
                    //日志
                    string EventType = "客户预删除";

                    int UserID = emp_id;
                    string UserName = emp_name;
                    string IPStreet = request.UserHostAddress;
                    int EventID = (id);
                    string EventTitle = ds.Tables[0].Rows[0]["Customer"].ToString();

                    var log = new sys_log();

                    log.Add_log(UserID, UserName, IPStreet, EventTitle, EventType, EventID, null);

                    return ("true");
                }
                return ("false");
            }
            return ("delfalse");
        }

        //regain            
        public string regain(string idlist)
        {
            idlist = PageValidate.InputText(request["idlist"], int.MaxValue);
            string[] arr = idlist.Split(',');

            DataSet ds = customer.GetList("id in (" + idlist.Trim() + ")");

            //日志   
            string EventType = "恢复删除客户";
            int UserID = emp_id;
            string UserName = emp_name;

            string IPStreet = request.UserHostAddress;

            int success = 0, failure = 0; //计数
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                bool isregain = customer.AdvanceDelete(int.Parse(arr[i]), 0,
                    DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                if (isregain)
                {
                    var log = new sys_log();
                    int EventID = int.Parse(ds.Tables[0].Rows[i]["id"].ToString());
                    string EventTitle = ds.Tables[0].Rows[i]["Customer"].ToString();
                    log.Add_log(UserID, UserName, IPStreet, EventTitle, EventType, EventID, null);
                    success++;
                }
                else
                {
                    failure++;
                }
            }
            return (string.Format("{0}恢复成功,{1}失败", success, failure));
        }

        public string del(string idlist)
        {
            bool canDel = false;

            if (uid == "admin")
            {
                canDel = true;
            }
            else
            {
                var getauth = new GetAuthorityByUid();
                canDel = getauth.GetBtnAuthority(emp_id.ToString(), "60");
                if (!canDel)
                    return ("auth");
            }

            if (canDel)
            {
                idlist = PageValidate.InputText(request["idlist"], int.MaxValue);
                string[] arr = idlist.Split(',');

                string EventType = "彻底删除客户";

                DataSet ds = customer.GetList("id in (" + idlist.Trim() + ")");

                bool canedel = true;
                if (uid != "admin")
                {
                    var dataauth = new GetDataAuth();
                    string txt = dataauth.GetDataAuthByid("1", "Sys_del", emp_id);

                    string[] arr1 = txt.Split(':');
                    switch (arr1[0])
                    {
                        case "none":
                            canedel = false;
                            break;
                        case "my":
                            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                            {
                                if (ds.Tables[0].Rows[i]["Employee_id"].ToString() == arr1[1])
                                    canedel = true;
                                else
                                    canedel = false;
                            }
                            break;
                        case "dep":
                            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                            {
                                if (ds.Tables[0].Rows[i]["Department_id"].ToString() == arr1[1])
                                    canedel = true;
                                else
                                    canedel = false;
                            }
                            break;
                        case "all":
                            canedel = true;
                            break;
                    }
                }
                if (canedel)
                {
                    var contact = new BLL.CRM_Contact();
                    var contract = new BLL.CRM_contract();
                    var order = new BLL.CRM_order();
                    var follow = new BLL.CRM_Follow();

                    int contactcount = 0, contractcount = 0, followcount = 0, ordercount = 0, success = 0, failure = 0;

                    //日志    
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        string cid = ds.Tables[0].Rows[i]["id"].ToString();

                        contractcount = contract.GetList(" Customer_id=" + int.Parse(cid)).Tables[0].Rows.Count;
                        contactcount = contact.GetList(" C_customerid=" + int.Parse(cid)).Tables[0].Rows.Count;
                        followcount = follow.GetList(" Customer_id=" + int.Parse(cid)).Tables[0].Rows.Count;
                        ordercount = order.GetList(" Customer_id=" + int.Parse(cid)).Tables[0].Rows.Count;

                        //context.Response.Write( string.Format("{0}联系人, {2}跟进, {3}订单，{1}合同 ", contactcount, contractcount, followcount, ordercount)+":"+(contactcount > 0 || contractcount > 0 || followcount > 0 || ordercount > 0)+" ");

                        if (contactcount > 0 || contractcount > 0 || followcount > 0 || ordercount > 0)
                        {
                            failure++;
                        }
                        else
                        {
                            bool isdel = customer.Delete(int.Parse(cid));
                            if (isdel)
                            {
                                success++;
                                int UserID = emp_id;
                                string UserName = emp_name;
                                string IPStreet = request.UserHostAddress;
                                int EventID = int.Parse(cid);
                                string EventTitle = ds.Tables[0].Rows[i]["Customer"].ToString();

                                var log = new sys_log();

                                log.Add_log(UserID, UserName, IPStreet, EventTitle, EventType, EventID, null);
                            }
                            else
                            {
                                failure++;
                            }
                        }
                    }
                    return (string.Format("{0}条数据成功删除，{1}条失败。|{1}", success, failure));
                }
                return ("delfalse");
            }
            return ("auth");
        }


        //validate website
        public string validate()
        {
            string company = request["T_company"];
            string customerid = request["T_cid"];
            if (string.IsNullOrEmpty(customerid) || customerid == "null")
                customerid = "0";

            DataSet ds =
                customer.GetList("Customer = N'" + PageValidate.InputText(company, 255) + "' and id!=" +
                                 int.Parse(customerid));
            //context.Response.Write(" Count:" + ds.Tables[0].Rows.Count);

            if (ds.Tables[0].Rows.Count > 0)
            {
                return ("false");
            }
            return ("true");
        }

        public string Compared()
        {
            string year1 = PageValidate.InputText(request["year1"], 50);
            string year2 = PageValidate.InputText(request["year2"], 50);
            string month1 = PageValidate.InputText(request["month1"], 50);
            string month2 = PageValidate.InputText(request["month2"], 50);

            DataSet ds = customer.Compared(year1, month1, year2, month2);

            string dt = GetGridJSON.DataTableToJSON(ds.Tables[0]);
            return (dt);
        }

        public string Compared_type()
        {
            string year1 = PageValidate.InputText(request["year1"], 50);
            string year2 = PageValidate.InputText(request["year2"], 50);
            string month1 = PageValidate.InputText(request["month1"], 50);
            string month2 = PageValidate.InputText(request["month2"], 50);

            DataSet ds = customer.Compared_type(year1, month1, year2, month2);

            string dt = GetGridJSON.DataTableToJSON(ds.Tables[0]);
            return (dt);
        }

        public string Compared_level()
        {
            string year1 = PageValidate.InputText(request["year1"], 50);
            string year2 = PageValidate.InputText(request["year2"], 50);
            string month1 = PageValidate.InputText(request["month1"], 50);
            string month2 = PageValidate.InputText(request["month2"], 50);

            DataSet ds = customer.Compared_level(year1, month1, year2, month2);

            string dt = GetGridJSON.DataTableToJSON(ds.Tables[0]);
            return (dt);
        }

        public string Compared_source()
        {
            string year1 = PageValidate.InputText(request["year1"], 50);
            string year2 = PageValidate.InputText(request["year2"], 50);
            string month1 = PageValidate.InputText(request["month1"], 50);
            string month2 = PageValidate.InputText(request["month2"], 50);

            DataSet ds = customer.Compared_source(year1, month1, year2, month2);

            string dt = GetGridJSON.DataTableToJSON(ds.Tables[0]);
            return (dt);
        }

        public string Compared_empcusadd()
        {
            string idlist = PageValidate.InputText(request["idlist"], int.MaxValue);
            string year1 = PageValidate.InputText(request["year1"], 50);
            string year2 = PageValidate.InputText(request["year2"], 50);
            string month1 = PageValidate.InputText(request["month1"], 50);
            string month2 = PageValidate.InputText(request["month2"], 50);

            if (idlist.Length < 1)
                idlist = "0";

            string[] pid = idlist.Split(';');
            string pidlist = "";
            for (int i = 0; i < pid.Length; i++)
            {
                int j = pid[i].IndexOf('p');
                if (j != -1)
                {
                    pidlist += pid[i].Replace("p", "") + ",";
                }
            }
            pidlist += "0";

            var post = new BLL.hr_post();
            DataSet dspost = post.GetList("id in(" + pidlist + ")");

            string emplist = "(";

            for (int i = 0; i < dspost.Tables[0].Rows.Count; i++)
            {
                emplist += dspost.Tables[0].Rows[i]["emp_id"] + ",";
            }
            emplist += "0)";

            DataSet ds = customer.Compared_empcusadd(year1, month1, year2, month2, emplist);

            string dt = GetGridJSON.DataTableToJSON(ds.Tables[0]);
            return (dt);
        }

        public string emp_customer()
        {
            string idlist = PageValidate.InputText(request["idlist"], int.MaxValue);
            string syear = request["syear"];

            if (idlist.Length < 1)
                idlist = "0";

            string[] pid = idlist.Split(';');
            string pidlist = "";
            for (int i = 0; i < pid.Length; i++)
            {
                int j = pid[i].IndexOf('p');
                if (j != -1)
                {
                    pidlist += pid[i].Replace("p", "") + ",";
                }
            }
            pidlist += "0";

            var post = new BLL.hr_post();
            DataSet dspost = post.GetList("id in(" + pidlist + ")");

            string emplist = "(";

            for (int i = 0; i < dspost.Tables[0].Rows.Count; i++)
            {
                emplist += dspost.Tables[0].Rows[i]["emp_id"] + ",";
            }
            emplist += "0)";


            //context.Response.Write(emplist);

            DataSet ds = customer.report_empcus(int.Parse(syear), emplist);

            string dt = GetGridJSON.DataTableToJSON(ds.Tables[0]);
            return (dt);
        }


        public void ToExcel()
        {
            string serchtxt = " isDelete=0 ";

            if (!string.IsNullOrEmpty(request["companyid"]))
                serchtxt += " and id =" + int.Parse(request["companyid"]);

            if (!string.IsNullOrEmpty(request["company"]))
                serchtxt += " and Customer like N'%" + PageValidate.InputText(request["company"], 255) + "%'";

            if (!string.IsNullOrEmpty(request["address"]))
                serchtxt += " and address like N'%" + PageValidate.InputText(request["address"], 255) + "%'";

            if (!string.IsNullOrEmpty(request["industry"]))
                serchtxt += " and industry like N'%" + PageValidate.InputText(request["industry"], 255) + "%'";

            if (!string.IsNullOrEmpty(request["tel"]))
                serchtxt += " and tel like N'%" + PageValidate.InputText(request["tel"], 255) + "%'";

            if (!string.IsNullOrEmpty(request["mobil"]))
                serchtxt += " and mobil like N'%" + PageValidate.InputText(request["mobil"], 255) + "%'";

            if (!string.IsNullOrEmpty(request["qq"]))
                serchtxt += " and QQ like N'%" + PageValidate.InputText(request["qq"], 255) + "%'";

            if (!string.IsNullOrEmpty(request["website"]))
                serchtxt += " and site like N'%" + PageValidate.InputText(request["website"], 255) + "%'";

            if (!string.IsNullOrEmpty(request["customertype"]))
                serchtxt += " and CustomerType_id = " + int.Parse(request["customertype_val"]);

            if (!string.IsNullOrEmpty(request["customerlevel"]))
                serchtxt += " and CustomerLevel_id = " + int.Parse(request["customerlevel_val"]);

            if (!string.IsNullOrEmpty(request["T_Provinces"]))
                serchtxt += " and Provinces_id = " + int.Parse(request["T_Provinces_val"]);

            if (!string.IsNullOrEmpty(request["T_City"]))
                serchtxt += " and City_id = " + int.Parse(request["T_City_val"]);

            if (!string.IsNullOrEmpty(request["department"]))
                serchtxt += " and Department_id = " + int.Parse(request["department_val"]);

            if (!string.IsNullOrEmpty(request["employee"]))
                serchtxt += " and Employee_id = " + int.Parse(request["employee_val"]);

            if (!string.IsNullOrEmpty(request["startdate"]))
                serchtxt += " and Create_date >= '" + PageValidate.InputText(request["startdate"], 255) + "'";

            if (!string.IsNullOrEmpty(request["enddate"]))
            {
                DateTime enddate = DateTime.Parse(request["enddate"]).AddHours(23).AddMinutes(59).AddSeconds(59);
                serchtxt += " and Create_date <= '" + enddate + "'";
            }

            if (!string.IsNullOrEmpty(request["startfollow"]))
                serchtxt += " and lastfollow >= '" + PageValidate.InputText(request["startfollow"], 255) + "'";

            if (!string.IsNullOrEmpty(request["endfollow"]))
            {
                DateTime enddate = DateTime.Parse(request["endfollow"]).AddHours(23).AddMinutes(59).AddSeconds(59);
                serchtxt += " and lastfollow <= '" + enddate + "'";
            }

            //权限
            serchtxt += DataAuth();

            DataSet ds = customer.ToExcel(0, serchtxt, "id desc");

            HSSFWorkbook book = new HSSFWorkbook();
            Sheet sheet = book.CreateSheet("客户信息表");

            CellStyle cellstyle = book.CreateCellStyle();

            cellstyle.BorderBottom = CellBorderType.THIN;
            cellstyle.BottomBorderColor = HSSFColor.BLACK.index;
            cellstyle.BorderLeft = CellBorderType.THIN;
            cellstyle.LeftBorderColor = HSSFColor.BLACK.index;
            cellstyle.BorderRight = CellBorderType.THIN;
            cellstyle.RightBorderColor = HSSFColor.BLACK.index; ;
            cellstyle.BorderTop = CellBorderType.THIN;
            cellstyle.TopBorderColor = HSSFColor.BLACK.index; ;
            cellstyle.Alignment = HorizontalAlignment.CENTER;
            cellstyle.VerticalAlignment = VerticalAlignment.CENTER;

            CellStyle cellstyle1 = book.CreateCellStyle();

            cellstyle1.BorderBottom = CellBorderType.THIN;
            cellstyle1.BottomBorderColor = HSSFColor.BLACK.index;
            cellstyle1.BorderLeft = CellBorderType.THIN;
            cellstyle1.LeftBorderColor = HSSFColor.BLACK.index;
            cellstyle1.BorderRight = CellBorderType.THIN;
            cellstyle1.RightBorderColor = HSSFColor.BLACK.index; ;
            cellstyle1.BorderTop = CellBorderType.THIN;
            cellstyle1.TopBorderColor = HSSFColor.BLACK.index; ;
            cellstyle1.Alignment = HorizontalAlignment.CENTER;
            cellstyle1.VerticalAlignment = VerticalAlignment.CENTER;

            Font font = book.CreateFont();
            font.Boldweight = short.MaxValue;
            cellstyle1.SetFont(font);

            string[] rowheader = { "客户名", "地址", "电话", "传真", "网站", "行业", "省份", "城市", "客户类型", "客户级别", "客户来源", "部门", "员工", "描述", "备注", "公私" };

            Row row0 = sheet.CreateRow(0);
            row0.Height = 30 * 20;
            for (int i = 0; i < rowheader.Length; i++)
            {
                Cell cell = row0.CreateCell(i);
                cell.SetCellValue(rowheader[i]);
                cell.CellStyle = cellstyle1;

            }

            if (ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Row irow = sheet.CreateRow(i + 1);

                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        Cell cell = irow.CreateCell(j);
                        cell.SetCellValue(ds.Tables[0].Rows[i][j].ToString());
                        cell.CellStyle = cellstyle;
                    }
                }
            }

            // 写入到客户端  
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            string filename = "客户信息表" + DateTime.Now.ToString("yyyyMMddhhmmss");
            filename = HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8);
            Context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", filename));
            Context.Response.Charset = "utf-8";
            Context.Response.ContentEncoding = System.Text.Encoding.UTF8;
            Context.Response.ContentType = "application/octet-stream";
            Context.Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }

        public int import()
        {
            string filepath = Context.Server.MapPath(@"~/file/customer/Customer.xls");

            Excel.Excel excel = new Excel.Excel(filepath, emp_id);
            int i = excel.Import();

            File.Delete(filepath);

            //日志
            int UserID = emp_id;
            string UserName = emp_name;
            string IPStreet = request.UserHostAddress;
            int EventID = emp_id;
            string EventTitle = string.Format("【{0}】导入客户", emp_name);

            var log = new sys_log();

            log.Add_log(UserID, UserName, IPStreet, EventTitle, "客户导入", EventID, null);
            return i;
        }

        public int c_count()
        {
            string serchtxt = " isDelete=0 ";

            if (!string.IsNullOrEmpty(request["T_employee1_val"]))
                serchtxt += string.Format(" and Employee_id={0}", PageValidate.InputText(request["T_employee1_val"], 50));

            if (!string.IsNullOrEmpty(request["T_customertype"]))
                serchtxt += " and CustomerType_id = " + int.Parse(request["T_customertype_val"]);

            if (!string.IsNullOrEmpty(request["T_customerlevel"]))
                serchtxt += " and CustomerLevel_id = " + int.Parse(request["T_customerlevel_val"]);

            if (!string.IsNullOrEmpty(request["T_CustomerSource"]))
                serchtxt += " and CustomerSource_id = " + int.Parse(request["T_CustomerSource_val"]);

            if (!string.IsNullOrEmpty(request["startdate"]))
                serchtxt += " and Create_date >= '" + PageValidate.InputText(request["startdate"], 255) + "'";

            if (!string.IsNullOrEmpty(request["enddate"]))
            {
                DateTime enddate = DateTime.Parse(request["enddate"]).AddHours(23).AddMinutes(59).AddSeconds(59);
                serchtxt += " and Create_date <= '" + enddate + "'";
            }

            if (!string.IsNullOrEmpty(request["startfollow"]))
                serchtxt += " and lastfollow >= '" + PageValidate.InputText(request["startfollow"], 255) + "'";

            if (!string.IsNullOrEmpty(request["endfollow"]))
            {
                DateTime enddate = DateTime.Parse(request["endfollow"]).AddHours(23).AddMinutes(59).AddSeconds(59);
                serchtxt += " and lastfollow <= '" + enddate + "'";
            }



            DataSet ds = customer.GetList(serchtxt);

            return (ds.Tables[0].Rows.Count);

        }

        private string DataAuth()
        {
            //权限            
            string uid = employee.uid;
            string returntxt = " 1=1";

            if (uid != "admin")
            {
                var dataauth = new GetDataAuth();
                string txt = dataauth.GetDataAuthByid("1", "Sys_view", emp_id);

                string[] arr = txt.Split(':');
                switch (arr[0])
                {
                    case "none":
                        returntxt = " 1=2";
                        break;
                    case "my":
                        returntxt = " ( privatecustomer='公客' or Employee_id=" + arr[1] + ")";
                        break;
                    case "dep":
                        if (string.IsNullOrEmpty(arr[1]))
                            returntxt = " ( privatecustomer='公客' or Employee_id=" + emp_id + ")";
                        else
                            returntxt = " ( privatecustomer='公客' or Department_id=" + arr[1] + ")";
                        break;
                    case "depall":
                        var dep = new BLL.hr_department();
                        DataSet ds = dep.GetAllList();
                        string deptask = GetTasks.GetDepTask(int.Parse(arr[1]), ds.Tables[0]);
                        string intext = arr[1] + "," + deptask;
                        returntxt = " ( privatecustomer='公客' or Department_id in (" + intext.TrimEnd(',') + "))";
                        break;
                }
            }

            return " and " + returntxt;
        }
    }
}